package com.sm.dao;

import com.sm.entity.Financial;
import com.sm.entity.FinancialRec;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Repository
public interface FinancialDao {

    @Select("select fid,ie,fname,createtime,createtime,uid,amount,comment,updatetime,kind,kindname as kindStr,month" +
            " from kind,financial where kind = kid and month = #{month}")
    @Results({
            @Result(property = "userInfo" ,column = "uid" ,one = @One(select = "com.sm.dao.UserDao.findByUid"))
    })
    public List<Financial> findAllFinancial(Integer month)throws Exception;

    //新增财务表
    @Options(useGeneratedKeys = true,keyProperty = "fid",keyColumn = "fid")
    @Insert("insert into financial(ie,fname,createtime,updatetime,uid,amount,comment,kind,month) " +
            "values(#{ie},#{fname},CURDATE(),#{updatetime},#{userInfo.uid},#{amount},#{comment},#{kind},#{month})")
    public Integer addFinancial(Financial financial)throws Exception;

    //修改财务表 修改了收支状态 更新时间 金额
    @Update("update financial set ie = #{ie},updatedate = #{updatedate},amount = #{amount} where fid = #{fid}")
    public void updateFinancial(Financial financial)throws Exception;

    //获取当天的所有的账单总表
    @Select("select * from financial_rec where date = #{dateStr}")
    public List<FinancialRec> findFinancialRec(String dateStr)throws Exception;

    //获取最近7天的利润
    @Select("select * from financial_rec where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date and sign = 1")
    public List<FinancialRec> findSevenDaysFRProfit()throws Exception;

    //获取最近7天的营收
    @Select("select * from financial_rec where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date and sign = 4")
    public List<FinancialRec> findSevenDaysFRIncome()throws Exception;

    //获得当前利润金额
    @Select("select amount from financial_rec where date =  CURDATE() and sign = 1")
    public Double findTodayProfit()throws Exception;

    //更新最近财务表
    @Update("update financial_rec set amount = #{amount} where date = #{dateStr} and sign = #{sign}")
    public void updateFinancialRec(FinancialRec financialRec)throws Exception;

    //查询最近7天营收 利润 总和
    @Select("select sign ,sum(amount) as amount from financial_rec where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date group by sign;")
    public List<FinancialRec> financialStatistics()throws Exception;

    //查询最新的账单
    @Select("select fid,ie,fname,createtime,createtime,uid,amount,comment,updatetime,kind,kindname as kindStr,month" +
            " from kind,financial where kind = kid and month = #{month} order by updatetime desc,fid desc limit #{limit}")
    @Results({
            @Result(property = "userInfo" ,column = "uid" ,one = @One(select = "com.sm.dao.UserDao.findByUid"))
    })
    public List<Financial> findNowFinancial(@Param("limit") Integer limit,@Param("month")Integer month)throws Exception;

    //获取当天账单数量
    @Select("select count(fid) from financial where createtime = CURDATE() ")
    public Integer findFinancialCount()throws Exception;

}
